<?php
//No direct access
defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.model');

class OwnserviceModelReport extends JModel
{
	/**
	 * Constructor
	 *
	 * @since 1.5
	 */

	function __construct(){
		parent::__construct();
	}

	/**
	 * Method get Transferred 
	 *
	 * @return object
	 * @since 1.5
	*/
	function getTransferred($user_id=0) {

		global $mainframe, $option;

		$post					= JRequest::get('Post');

		$filter_order			= $mainframe->getUserStateFromRequest($option.'.transferredrecord.filter_order', 'filter_order', 't.requested_date', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.transferredrecord.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');

		$where = array();
		$where[] = 't.state = 4';

		if($user_id)
			$where[] = 't.user_id='.$user_id;

		switch($post['period']):
			case "1":
				$where[] = ' ( t.created >= '.$this->_db->Quote($post['date'].' 00:00:00').' AND t.created <='.$this->_db->Quote($post['date'].' 23:59:59').' )';
			break;
			case "2":
				$where[] = 't.created >= '.$this->_db->Quote(date('Y').'-'.$post['month'].'-1 00:00:00').' AND t.created <='.$this->_db->Quote(date('Y').'-'.$post['month'].'-'.date("t", mktime(0,0,0,$post['month'],1,date('Y'))).' 23:59:59').' ';
			break;
			case "3":
				$where[] = ' ( t.created >= '.$this->_db->Quote($post['from'].' 00:00:00').' AND t.created <='.$this->_db->Quote($post['to'].' 23:59:59').' )';
			break;
		endswitch;

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_Dir;

		$query = ' SELECT t.*, b.name AS branchname, b.code AS b_code, b.address AS b_address,b.city AS b_city, b.state AS b_state, '
				.' b.postcode AS b_postcode, c.code AS r_code, c.sign, c.iscore, u.name, u.username, u.email, p.id AS proid, p.birthday, recei.lastname, '
				.' p.address AS p_address,p.city AS p_city, p.state AS p_state, p.postcode AS p_postcode, p.country AS p_country, '
				.' p.phone_no AS p_phone, p.id_type AS p_id_type, p.id_number AS p_id_number, p.issuer AS p_issuer, p.issuer_other, p.expire_date, a.account_name, '
				.' recei.address AS recei_address,recei.city AS recei_city, recei.state AS recei_state, recei.postcode AS recei_postcode, '
				.' recei.country AS recei_country, a.address AS a_address, a.city AS a_city, a.state AS a_state, recei.firstname, '
				.' a.postcode AS a_postcode, a.country AS a_country, a.phone_no AS a_phone, a.email AS a_email, a.bank_name, a.bank_other, a.branch_name, a.branch_city '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_users AS recei ON recei.user_id=t.transfer_id'
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account and a.user_id=u.id'
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
				.$where
				.' GROUP BY t.id '
				/*.' ORDER BY t.created DESC, t.modified DESC'*/
				.$orderby;

		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}

	function getStaffTransferred($id) {

		$post = JRequest::get('Post');

		$where = array();
		$where[] = 't.state = 4';

		$where[] = 't.transfer_id='.$id;

		switch($post['period']):
			case "1":
				$where[] = ' ( t.created >= '.$this->_db->Quote($post['date'].' 00:00:00').' AND t.created <='.$this->_db->Quote($post['date'].' 23:59:59').' )';
			break;
			case "2":
				$where[] = 't.created >= '.$this->_db->Quote(date('Y').'-'.$post['month'].'-1 00:00:00').' AND t.created <='.$this->_db->Quote(date('Y').'-'.$post['month'].'-'.date("t", mktime(0,0,0,$post['month'],1,date('Y'))).' 23:59:59').' ';
			break;
			case "3":
				$where[] = ' ( t.created >= '.$this->_db->Quote($post['from'].' 00:00:00').' AND t.created <='.$this->_db->Quote($post['to'].' 23:59:59').' )';
			break;
		endswitch;

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = ' SELECT t.*, b.name AS branchname, b.code AS b_code, b.address AS b_address,b.city AS b_city, b.state AS b_state, '
				.' b.postcode AS b_postcode, c.code AS r_code, c.sign, c.iscore, u.name, u.username, u.email, p.id AS proid, p.birthday, recei.firstname AS recei_firstname, recei.lastname AS recei_lastname, '
				.' p.address AS p_address,p.city AS p_city, p.state AS p_state, p.postcode AS p_postcode, p.country AS p_country, '
				.' p.phone_no AS p_phone, p.id_type AS p_id_type, p.id_number AS p_id_number, p.issuer AS p_issuer, p.expire_date, a.account_name, '
				.' recei.address AS recei_address,recei.phone_no AS recei_phone, recei.city AS recei_city, recei.state AS recei_state, recei.postcode AS recei_postcode, '
				.' recei.country AS recei_country, a.address AS a_address, a.city AS a_city, a.state AS a_state, recei.firstname, '
				.' a.postcode AS a_postcode, a.country AS a_country, a.phone_no AS a_phone, a.email AS a_email, a.bank_name, a.branch_name '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_users AS recei ON recei.user_id=t.transfer_id'
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account'
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id= r.currency_id '
				.$where
				.' GROUP BY t.id '
				.' ORDER BY t.created DESC, t.modified DESC';

		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}


	/**
	 * Method get user
	 *
	 * @return object
	 * @since 1.5
	*/
	function getUser($id) {

		$query = ' SELECT p.*, u.name, u.username, u.email FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE u.id='.$id.' AND u.block = 0 ';
		$this->_db->setQuery($query);
		$profile = $this->_db->loadObject();

		return $profile;
	}

	/**
	 * Method get users
	 *
	 * @return object
	 * @since 1.5
	*/
	function getUsers() {

		$user =& JFactory::getUser();

		$where = array();

		$where[] = 'u.block=0';
		$where[] = 'p.group_id = 1';
	
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = 'SELECT u.id, u.name '
				.' FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' LEFT JOIN #__ownservice_groups AS g ON g.id=p.group_id '
				.$where
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectList();

		return $users;
	}

	/**
	 * Method to get accounts of user
	 *
	 * @return object
	 * @since 1.5
	*/
	function getAccounts($user_id) {

		$query = ' SELECT * FROM #__ownservice_accounts WHERE user_id='.$user_id.' AND 	deleted=0 ';
		$this->_db->setQuery($query);
		$accounts = $this->_db->loadObjectList();

		return $accounts;
	}

	/**
	 * Method to get staff transfer
	 *
	 * @return object
	 * @since 1.5
	*/
	function getStaffTransfer() {

		$user =& JFactory::getUser();

		$where = array();

		$where[] = 'u.block=0';
		$where[] = 'p.group_id = 3';
	
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = 'SELECT u.id, u.name, u.username '
				.' FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' LEFT JOIN #__ownservice_groups AS g ON g.id=p.group_id '
				.$where
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$stafftransfers = $this->_db->loadObjectList();

		return $stafftransfers;
	}

	/**
	 * Method get total users 
	 *
	 * @return int
	 * @since 1.5
	*/
	function getTotalUsers()
	{
		$query = ' SELECT p.*, u.username, u.email, g.name AS groupname '
				.' FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' LEFT JOIN #__ownservice_groups AS g ON g.id=p.group_id '
				.' WHERE p.group_id = 1 '
				.' AND p.deleted = 0 '
				.' AND u.block = 0 '
				;
		$total = $this->_getListCount($query);

		return $total;
	}
	
	/**
	 * Method to fetch members according to a given mask
	 * 
	 * @return object
	 * @since 1.0
	 */
	function getmembers($mask="")
	{
		$user		=&JFactory::getUser();
		$ownuser	=& Ownservice::getUser();
	
		$where		=array();
		$where[]	='u.block = 0';
		
		if($ownuser->get('branch_id')){
			$where[]	='p.branch_id='.$ownuser->get('branch_id');
		} else {
			//check level 
			if( ($user->gid < 25) && ($ownuser->get('level_id') < 5) ){
				$where[]	='p.branch_id= 0';
			}
		}
		
		if($mask!="")
		{
			$where[] = "(u.name like '%$mask%' OR u.username like '%$mask%')";
		}
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );
		
		$query = 'SELECT u.* FROM #__users AS u '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
				.$where
				.' GROUP BY u.id '
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectlist();
		
		return $users;
	}

	/**
	 * Method to remove transactions
	 *
	 * @access	public
	 * @return	boolean	True on success
	 * @since	1.0
	 */
	function delete($cid = array())
	{
		$result = false;

		if (count( $cid ))
		{
			$cids = implode( ',', $cid );

			$query = 'DELETE FROM #__ownservice_transactions '
						. ' WHERE id IN ('. $cids .')';

			$this->_db->setQuery( $query );
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}

		return true;
	}
}